package com.sx.service;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;

import com.dfec.utils.DruidPlugin;

public class NotifacationSendService {
	QueryRunner qr=new QueryRunner(DruidPlugin.getDataSource());
	List<Object> list=null;
    String sql="";
	public int commitSend(String title, String content, int hours, String city, String country, String town,
			String phone, String name, String post) throws SQLException {
		sql = "INSERT INTO d_town_notification "
				+ "(not_title,not_content,not_value_hours,send_city,send_country,send_town,send_phone,send_name,send_post,create_time) "
				+ "VALUES ('" + title + "','" + content + "'," + hours + ",'" + city + "','" + country + "','" + town
				+ "','" + phone + "','" + name + "','" + post + "',NOW())";
		return qr.update(sql);
	}

	public List<Object> getListData(int page, int size, String town, String phone) throws SQLException {
		list=new ArrayList<>();
		int num=((page-1)*size);
    	int tonum=page*size;
		sql = "SELECT * ,"
				+ "(SELECT COUNT(b.notification_id) from d_town_notification_log as b where a.not_id=b.notification_id AND b.reader_phone='"
				+ phone + "') as is_read,"
				+ "(select COUNT(b.notification_id) from d_town_notification_log as b where a.not_id=b.notification_id) as reader_count "
				+ "FROM d_town_notification as a WHERE a.send_town='" + town + "' order by not_id desc limit " + num
				+ "," + tonum;
		 list.add(qr.query(sql, new MapListHandler()));
		return list;
	}

	public List<Object> getContent(String rPhone, String rName, String rCity, String rCountry, String rTown,
			int not_id) throws SQLException {
		list=new ArrayList<>();
		sql = "SELECT * FROM d_town_notification_log a WHERE a.notification_id=" + not_id + " AND a.reader_phone='"
				+ rPhone + "'";
		List<Map<String, Object>> qrr = qr.query(sql,new MapListHandler());	
		boolean completeInsert = true;
		// 查询当前用户是否已读
			if (qrr.size()==0) {
				if (insertNotificationLog(rPhone, rName, rCity, rCountry, rTown, not_id)) {
					completeInsert = true;
				} else {
					completeInsert = false;
				}
			}
		// 查询通知详细内容
		if (completeInsert) {
				String readers = getAllReaders(not_id);
				sql = "SELECT * FROM d_town_notification as a where a.not_id=" + not_id;
				List<Map<String, Object>> list1 = qr.query(sql, new MapListHandler());
				Map<String, Object> map=new HashMap<String, Object>();
				map.put("readers",readers.length()>0? readers.substring(0, readers.length() - 1):"");
				list1.add(map);
				list.add(list1);
				
		}
		return list;
	}

	/**
	 * @Title: insertNotificationLog
	 * @Description: 在通知浏览记录表中插入数据
	 * @param rPhone
	 *            阅读者电话
	 * @param rName
	 *            阅读者姓名
	 * @param rCity
	 *            阅读者城市
	 * @param rCountry
	 *            阅读者区
	 * @param rTown
	 *            阅读者镇
	 * @param not_id
	 *            通知id
	 * @return boolean 成功返回true，否则返回false
	 * @throws SQLException 
	 */
	private boolean insertNotificationLog(String rPhone, String rName, String rCity, String rCountry, String rTown,
			int not_id) throws SQLException {
		sql = "INSERT INTO d_town_notification_log (reader_phone,reader_name,reader_city,reader_country,reader_town,read_time,notification_id) "
				+ "VALUES ('" + rPhone + "','" + rName + "','" + rCity + "','" + rCountry + "','" + rTown + "',NOW(),"
				+ not_id + ")";
		
		return	  qr.update(sql) > 0 ? true : false;
	}

	/**
	 * @Title: getAllReaders
	 * @Description: 获取某条通知的所有浏览者
	 * @param not_id
	 *            通知id
	 * @return: String 所有浏览者
	 * @throws SQLException 
	 */
	private String getAllReaders(int not_id) throws SQLException {
		String readers = "";
		sql = "SELECT a.reader_name as name FROM d_town_notification_log as a WHERE a.notification_id=" + not_id;
		System.err.println(sql);
		List<Map<String, Object>> res = qr.query(sql, new MapListHandler());
		for (int i = 0; i < res.size(); i++) {
			readers += (res.get(i).get("reader_name") + ",");
			
		}
		return readers;
	}
}
